package com.violation.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ViolationDAO implements ViolationDAO_interface{
	private static DataSource ds = null;
	static {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CARPOOL");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	private static final String GET_ONE_BY_ID = 
			"SELECT name, description FROM VIOLATION WHERE violation_id=?";
	
	private static final String GET_ALL =
			"SELECT violation_id, name, description FROM VIOLATION";
	@Override
	public ViolationVO getByViolation_id(Integer violation_id) {
		ViolationVO violation = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ONE_BY_ID);
			pstmt.setInt(1, violation_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				violation = new ViolationVO();
				violation.setName(rs.getString("name"));
				violation.setDescription(rs.getString("description"));
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. " + e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return violation;
	}
	@Override
	public List<ViolationVO> getAll() {
		List<ViolationVO> list = new ArrayList<ViolationVO>();
		ViolationVO v = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				v = new ViolationVO();
				v.setViolation_id(rs.getInt("violation_id"));
				v.setName(rs.getString("name"));
				v.setDescription(rs.getString("description"));
				list.add(v);
			}			
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. " + e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}	
}
